package com.zbystudy.core.vo;

import com.zbystudy.core.TableInit;
import com.zbystudy.factory.sqlConnectFactory;
import com.zbystudy.util.ConnCloseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

import static com.zbystudy.util.MapUtil.isKeyBlank;
import static com.zbystudy.util.MapUtil.reverseMap;
import static org.apache.commons.lang3.StringUtils.*;


/**
 * Created By zby on 16:12 2019/4/3
 * 创建表
 */
public class CreationTable {

    /**
     * Created By zby on 16:15 2019/4/3
     * 判断表是否存在
     */
    public static boolean existsTable(String tableName) {
        if (isBlank(tableName)) {
            return false;
        }
        String sql = "SELECT column_name FROM information_schema.columns WHERE table_name=?";
        Connection conn = sqlConnectFactory.createConnect();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnCloseUtil.closeConn(conn, ps, rs);
        }
        return false;
    }


    /**
     * Created By zby on 10:48 2019/4/8
     * 删除表
     */
    public static boolean dropTable(String tableName) {
        if (isBlank(tableName)) {
            return false;
        }
        String sql = "DROP TABLE " + tableName;
        Connection conn = sqlConnectFactory.createConnect();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            int result = ps.executeUpdate();
            return result == 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnCloseUtil.closeConn(conn, ps);
        }
        return false;
    }

    /**
     * Created By zby on 0:12 2019/4/9
     * <p>
     * 批量删除
     */
    public static void batchDropTable(Map<String, StringBuilder> tableSqls) {
        if (isKeyBlank(tableSqls)) {
            throw new RuntimeException("表名为空，请核查后再删除表");
        }
        for (Map.Entry<String, StringBuilder> entry : tableSqls.entrySet()) {
            String tableName = entry.getKey();
//            表不存在，跳过此循环
            if (!existsTable(tableName)) {
                continue;
            }
            dropTable(entry.getKey());
        }
    }

    /**
     * Created By zby on 9:30 2019/4/8
     * 创建数据表
     */
    public synchronized static boolean batchCreateTable() {
//        是否忽略已存在的表
        String ignoreExistTable = sqlConnectFactory.getProperties().getProperty("jdbc.ignoreExistTable");
        Connection conn = sqlConnectFactory.createConnect();
        boolean tranSuccess = false;
        try {
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            Map<String, StringBuilder> tableSQLs = TableInit.getDbTableMap();
            if (isNotBlank(ignoreExistTable) && ignoreExistTable.equalsIgnoreCase("true")) {
//                如果表名为空，则无法删除
                if (isKeyBlank(tableSQLs)) {
                    return false;
                }
                for (Map.Entry<String, StringBuilder> entry : tableSQLs.entrySet()) {
                    boolean tableExists = existsTable(entry.getKey());
//              如果表存在，则跳过循环
                    if (tableExists) {
                        continue;
                    }
                    tranSuccess = CreateTable(entry.getKey(), conn, entry.getValue().toString());
                }
            } else {
//                map数据反转
                Map<String, StringBuilder> reverseTableSqls = reverseMap(tableSQLs);
//                如果表名为空，则无法删除
                if (isKeyBlank(reverseTableSqls)) {
                    return false;
                }
//                先删除所有表，在创建表
                batchDropTable(reverseTableSqls);
                for (Map.Entry<String, StringBuilder> entry : tableSQLs.entrySet()) {
                    tranSuccess = CreateTable(entry.getKey(), conn, entry.getValue().toString());
                }
            }
            if (tranSuccess) {
                conn.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnCloseUtil.closeConn(conn);
        }
        return tranSuccess;
    }

    /**
     * Created By zby on 9:30 2019/4/9
     * 创建数据表
     *
     * @param tableName 表名
     * @param conn      数据库连接对象
     * @param sql       创建表的执行语句
     */
    public static boolean CreateTable(String tableName, Connection conn, String sql) {
        if (conn != null && isNotBlank(sql)) {
            PreparedStatement ps = null;
            try {
                ps = conn.prepareStatement(sql);
                return ps.executeUpdate() == 0 ? true : false;
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("添加表tableName=" + tableName + "失败");
            } finally {
                ConnCloseUtil.closeState(ps);
            }
        }
        return false;
    }

}
